CREATE   PROCEDURE [dbo].[asi_SalesTeamAddDateRange] (@salesTeamKey uniqueidentifier, @newStartDate datetime, @userKey uniqueidentifier)
AS
/*
This storage procedure is used by Asi.Business.Commerce.SalesTeam.SalesTeamUtilities.AddRange method
to add a new date range for an existing Sales Team.
Parameters:
    @salesTeamKey is Guid of the Group object that a new Date Rage is to be added.
    @newStartDate is a DateTime value that the new Date Range is to start from
    @userKey is the Guid of the User object who is creating the new Date Range.
Return values:
    -1 - There are no existing GroupMemberDetails for the Group specified
    -2 - The new Date Range occurs prior to the first GroupMemberDetails date range
    -4 - The Start Date specified is either
        a. the date directly preceeding an existing start date
        b. the date of an existing start date
        c. the date directly following an existing start date
*/
BEGIN
SET NOCOUNT ON

DECLARE @DetailCount int

-- Determine if there are any existing GroupMemberDetail records for this sales team if not return an error code of -1
IF (SELECT COUNT(*) FROM GroupMemberDetail WHERE GroupKey = @salesTeamKey) < 1
BEGIN
    SELECT -1
    RETURN -1
END
-- Determine if @newStartDate occurs prior to the first date range for the sales team and if so return an error code of -2
IF (DATEDIFF(day,@newStartDate,(SELECT MIN(EffectiveDate) FROM GroupMemberDetail WHERE GroupKey = @salesTeamKey)) > 0)
BEGIN
    SELECT -2
    RETURN -2
END
-- Determine if @newStartDate equals an existing start date, the date following an existing start date, or an existing expiration date for this Sales Team
SELECT @DetailCount = COUNT(*) FROM GroupMemberDetail WHERE GroupKey = @salesTeamKey AND DATEDIFF(day,EffectiveDate,@newStartDate) IN (0,1) OR DATEDIFF(day,@newStartDate,ExpirationDate) = 0
-- If we find existing Detail records that means the @newStartDate is invalid.
IF @DetailCount > 0
BEGIN
    SELECT -4
    RETURN -4
END

BEGIN TRANSACTION
CREATE TABLE #tmpGroupMemberDetail (GroupMemberKey uniqueidentifier,GroupRoleKey uniqueidentifier,EffectiveDate datetime,
    ExpirationDate datetime,TypeSpecificData nvarchar(250),TypeSpecificKey uniqueidentifier,TypeSpecificFlag bit,
    CreatedOn datetime,CreatedByUserKey uniqueidentifier,UpdatedOn datetime,UpdatedByUserKey uniqueidentifier,
    TypeSpecificInt int,GroupMemberStatusCode nvarchar(4),IsActive bit,GroupMemberDetailKey uniqueidentifier,
    GroupKey uniqueidentifier)

-- Get the EffectiveDate of the next range if one exists
DECLARE @nextEffectiveDate datetime
SELECT @nextEffectiveDate = MIN(EffectiveDate) FROM GroupMemberDetail WHERE GroupKey = @salesTeamKey AND EffectiveDate > @newStartDate

-- Create the new range records in the temp table
INSERT INTO #tmpGroupMemberDetail(GroupMemberKey,GroupRoleKey,EffectiveDate,ExpirationDate,TypeSpecificData,TypeSpecificKey,TypeSpecificFlag,CreatedOn,CreatedByUserKey,
    UpdatedOn,UpdatedByUserKey,TypeSpecificInt,GroupMemberStatusCode,IsActive,GroupMemberDetailKey,GroupKey)
Select GroupMemberKey,GroupRoleKey,@newStartDate,DATEADD(day,-1,@nextEffectiveDate),TypeSpecificData,TypeSpecificKey,TypeSpecificFlag,GetDate(),@userKey,
    GetDate(),@userKey,TypeSpecificInt,GroupMemberStatusCode,IsActive,NEWID(),GroupKey
From GroupMemberDetail
WHERE GroupKey = @salesTeamKey AND
(@newStartDate BETWEEN EffectiveDate AND ExpirationDate OR (ExpirationDate IS NULL AND DATEDIFF(day,EffectiveDate,@newStartDate) > 0))
Order by EffectiveDate, GroupMemberKey

-- Set the expiration date of the existing records with the @newStartDate less 1 day
UPDATE GroupMemberDetail
SET ExpirationDate = DATEADD(day,-1,@newStartDate), UpdatedOn = GetDate(), UpdatedByUserKey = @userKey
WHERE GroupKey = @salesTeamKey AND
(@newStartDate BETWEEN EffectiveDate AND ExpirationDate OR (ExpirationDate IS NULL AND DATEDIFF(day,EffectiveDate,@newStartDate) > 0))

-- Insert the new records into the database table
INSERT INTO GroupMemberDetail(GroupMemberKey,GroupRoleKey,EffectiveDate,ExpirationDate,TypeSpecificData,TypeSpecificKey,TypeSpecificFlag,CreatedOn,CreatedByUserKey,
    UpdatedOn,UpdatedByUserKey,TypeSpecificInt,GroupMemberStatusCode,IsActive,GroupMemberDetailKey,GroupKey)
Select GroupMemberKey,GroupRoleKey,EffectiveDate,ExpirationDate,TypeSpecificData,TypeSpecificKey,TypeSpecificFlag,CreatedOn,CreatedByUserKey,
    UpdatedOn,UpdatedByUserKey,TypeSpecificInt,GroupMemberStatusCode,IsActive,GroupMemberDetailKey,GroupKey
From #tmpGroupMemberDetail
COMMIT

SELECT 0

DROP TABLE #tmpGroupMemberDetail

SET NOCOUNT OFF
END

GO